import pandas as pd
from openpyxl import load_workbook
from utils import add_data,add_mutable_data


def copy_previous_period_number(previous_tb_file, tb_file,previous_sheet_name,now_sheet_name,copy_column,paste_cell,how,formula,mutable=False,to_column=None,index_col=None):
    '''
    This function is used to copy the previous period number from previous_tb_file to tb_file.
    :param previous_tb_file: 上期审定表文件路径
    :param tb_file: 本期审定表文件路径
    :param previous_sheet_name: 上期审定表sheet名称
    :param now_sheet_name: 本期审定表sheet名称
    :param copy_column: 复制的列名，可以是字符串或列表
    :param paste_cell: 粘贴的单元格，如"A2"
    :param how: 粘贴方式，可以是"column"或"row"
    :param formula: 是否覆盖有公式的单元格，True或False
    :param mutable: 是否复制mutable_data，True或False
    :param to_column: 复制到哪一列，如果mutable=True，则需要指定
    :param index_col: 复制mutable_data时，需要指定index_col
    :return:
    '''

    tb_wb = load_workbook(tb_file)
    ws = tb_wb[now_sheet_name]
    if index_col is not None:
        df_to = pd.read_excel(tb_file, sheet_name=now_sheet_name,dtype={index_col:str})
        df_from = pd.read_excel(previous_tb_file, sheet_name=previous_sheet_name,dtype={index_col:str})
    else:
        df_to = pd.read_excel(tb_file, sheet_name=now_sheet_name)
        df_from = pd.read_excel(previous_tb_file, sheet_name=previous_sheet_name)
    if mutable:
        # 如果mutable=True，则需要复制mutable_data
        # df_from,df_to,from_col,to_col,index_col,wb,sheet_name,start_cell,how="column",formula=True
        add_mutable_data(df_from, df_to, copy_column,to_column,index_col,tb_wb,now_sheet_name,paste_cell,how,formula)
    else:
        # 如果copy_column是list，则需要循环复制
        if isinstance(copy_column, list):
            copy_data = []
            for column in copy_column:
                copy_data.append(df_from[column].tolist())
            add_data(ws, copy_data, paste_cell, "matrix",formula)
        else:
            copy_data = df_from[copy_column].tolist()
            add_data(ws, copy_data, paste_cell, how,formula)

    tb_wb.save(tb_file)
    tb_wb.close()

def copy_TB_cash_owners(previous_tb_file,tb_file):
    '''
    、
    :param previous_tb_file: 上期审定表文件路径
    :param tb_file: 本期审定表文件路径
    :return:
    '''

    print("复制上期审定TB")
    copy_previous_period_number(previous_tb_file, tb_file,"本期TB","上期TB","审定数","E2","column",True)
    print("复制上期现金流量表")
    copy_previous_period_number(previous_tb_file, tb_file,"现金流量表","现金流量表","本年金额","C2","column",True)
    print("复制上期所有者权益变动表")
    df_previous_owner_equity = pd.read_excel(previous_tb_file, sheet_name="上期所有者权益变动表")
    copy_column = df_previous_owner_equity.columns.tolist()[1:]
    copy_previous_period_number(previous_tb_file, tb_file,"本期所有者权益变动表","上期所有者权益变动表",copy_column,"B2","column",True)



def copy_balance_sheet(previous_tb_file,tb_file):
    '''
    复制上期资产负债表
    :param previous_tb_file: 上期审定表文件路径
    :param tb_file: 本期审定表文件路径
    :return:
    '''

    print("复制货币资金")
    copy_previous_period_number(previous_tb_file, tb_file,"货币资金","货币资金","期末余额","C2","column",True)
    print("复制应收票据分类新金融工具准则")
    copy_previous_period_number(previous_tb_file, tb_file,"应收票据分类新金融工具准则","应收票据分类新金融工具准则",["期末账面余额","期末坏账准备"],"E2","column",True)
    print("复制应收账款")
    copy_previous_period_number(previous_tb_file, tb_file,"应收账款期末数","应收账款期初数",["期末账面余额","期末账面余额比例","期末坏账准备","期末坏账准备比例"],"B2","column",True)
    print("复制应收账款账龄情况")
    copy_previous_period_number(previous_tb_file, tb_file,"应收账款账龄情况","应收账款账龄情况","期末余额","C2","column",True)
    print("复制应收款项融资")
    copy_previous_period_number(previous_tb_file, tb_file,"应收款项融资","应收款项融资","期末余额","C2","column",True)
    print("复制预付账款账龄明细")
    copy_previous_period_number(previous_tb_file, tb_file,"预付账款账龄明细","预付账款账龄明细",["期末余额","期末比例(%)","期末减值准备"],"F2","column",True)
    print("复制应收利息分类")
    copy_previous_period_number(previous_tb_file, tb_file,"应收利息分类","应收利息分类","期末余额","C2","column",True,mutable=True,to_column="期初余额",index_col="项目")
    print("复制其他应收款期初数")
    copy_previous_period_number(previous_tb_file, tb_file,"其他应收款期末数","其他应收款期初数",["期末账面余额","期末账面余额比例","期末坏账准备"],"B2","matrix",True)
    print("复制其他应收款账龄情况")
    copy_previous_period_number(previous_tb_file, tb_file,"其他应收款账龄情况","其他应收款账龄情况","期末余额","C2","column",True)
    print("复制存货明细情况")
    copy_previous_period_number(previous_tb_file, tb_file,"存货明细情况","存货明细情况",["期末余额","期末跌价准备"],"E2","matrix",True,mutable=True,to_column=["期初余额","期初跌价准备"],index_col="项目")
    print("复制合同资产情况")
    copy_previous_period_number(previous_tb_file, tb_file,"合同资产情况","合同资产情况",["期末余额","期末减值准备"],"E2","column",True)
    print("复制固定资产清理")
    copy_previous_period_number(previous_tb_file, tb_file,"固定资产清理","固定资产清理","期末余额","C2","column",True,mutable=True,to_column="期初余额",index_col="项目")
    print("复制应付票据")
    copy_previous_period_number(previous_tb_file, tb_file,"应付票据","应付票据","期末余额","C2","column",True,mutable=True,to_column="期初余额",index_col="项目")
    print("复制应付账款")
    copy_previous_period_number(previous_tb_file, tb_file, "应付账款", "应付账款", "期末余额", "C2", "column", True)
    print("复制预收款项")
    copy_previous_period_number(previous_tb_file, tb_file, "预收款项", "预收款项", "期末余额", "C2", "column", True, mutable=True, to_column="期初余额", index_col="项目")
    print("复制预收款项账龄表")
    copy_previous_period_number(previous_tb_file, tb_file, "预收款项账龄表", "预收款项账龄表", "期末余额", "C2", "column", True)
    print("复制合同负债")
    copy_previous_period_number(previous_tb_file, tb_file, "合同负债", "合同负债", "期末余额", "C2",
                                "column", True, mutable=True, to_column="期初余额", index_col="项目")
    print("复制应付利息")
    copy_previous_period_number(previous_tb_file, tb_file, "应付利息", "应付利息", "期末余额", "C2", "column", True, mutable=True, to_column="期初余额", index_col="项目")
    print("复制其他应付款项")
    copy_previous_period_number(previous_tb_file, tb_file, "其他应付款项", "其他应付款项", "期末余额", "C2", "column", True, mutable=True, to_column="期初余额", index_col="项目")
    print("复制持有待售负债")
    copy_previous_period_number(previous_tb_file, tb_file, "持有待售负债", "持有待售负债", "期末余额", "C2", "column",
                                True, mutable=True, to_column="期初余额", index_col="项目")
    print("复制一年内到期的非流动负债")
    copy_previous_period_number(previous_tb_file, tb_file, "一年内到期的非流动负债", "一年内到期的非流动负债", "期末余额", "C2", "column",
                                True, mutable=True, to_column="期初余额", index_col="项目")
    print("复制长期应付款")
    copy_previous_period_number(previous_tb_file, tb_file, "长期应付款", "长期应付款",
                                "期末余额", "C2", "column",
                                True, mutable=True, to_column="期初余额", index_col="项目")
    print("复制长期应付职工薪酬明细情况")
    copy_previous_period_number(previous_tb_file, tb_file, "长期应付职工薪酬明细情况", "长期应付职工薪酬明细情况",
                                "期末余额", "C2", "column",
                                True, mutable=True, to_column="期初余额", index_col="项目")
    print("复制其他非流动负债")
    copy_previous_period_number(previous_tb_file, tb_file, "其他非流动负债", "其他非流动负债",
                                "期末余额", "C2", "column",
                                True, mutable=True, to_column="期初余额", index_col="项目")
    print("复制实收资本")
    copy_previous_period_number(previous_tb_file, tb_file, "实收资本", "实收资本",
                                "期末投资金额", "B2", "column",
                                True, mutable=True, to_column="期初投资金额", index_col="投资者名称")

    print("复制资本公积")
    copy_previous_period_number(previous_tb_file, tb_file, "资本公积", "资本公积",
                                "期末余额", "B2", "column",True)
    print("复制专项储备")
    copy_previous_period_number(previous_tb_file, tb_file, "专项储备", "专项储备",
                                "期末余额", "B2", "column", True, mutable=True, to_column="期初余额", index_col="项目")
    print("复制盈余公积")
    copy_previous_period_number(previous_tb_file, tb_file, "盈余公积", "盈余公积",
                                "期末余额", "B2", "column", True, mutable=True, to_column="期初余额", index_col="项目")
    print("复制完成")


def copy_profit_sheet(previous_tb_file,tb_file):
    '''
    复制上期利润表
    :param previous_tb_file: 上期审定表文件路径
    :param tb_file: 本期审定表文件路径
    :return:
    '''

    print("复制财务费用")
    copy_previous_period_number(previous_tb_file, tb_file,"财务费用","财务费用","本期发生额","C2","column",True,mutable=True,to_column="上期发生额",index_col="项目")
    print("复制将净利润调节为经营活动现金流量")
    copy_previous_period_number(previous_tb_file, tb_file,"将净利润调节为经营活动现金流量","将净利润调节为经营活动现金流量","本期发生额","C2","column",True)
    print("复制母公司基本情况")
    copy_previous_period_number(previous_tb_file, tb_file,"母公司基本情况","母公司基本情况",["母公司名称","注册地","业务性质","注册资本","母公司对本公司的持股比例(%)", "母公司对本公司的表决权比例(%)"],"A2","matrix",True)
    print("复制其他关联方情况")
    copy_previous_period_number(previous_tb_file, tb_file,"其他关联方情况","其他关联方情况",["其他关联方名称","其他关联方与本公司关系"],"A2","matrix",True)
    print("复制采购商品接收劳务")
    copy_previous_period_number(previous_tb_file, tb_file,"采购商品接收劳务","采购商品接收劳务","本期数","D2","column",True,mutable=True,to_column="上年同期数",index_col="关联方")
    print("复制出售商品提供劳务")
    copy_previous_period_number(previous_tb_file, tb_file, "出售商品提供劳务", "出售商品提供劳务", "本期数", "D2",
                                "column", True, mutable=True, to_column="上年同期数", index_col="关联方")
    print("复制本公司作为出租方")
    copy_previous_period_number(previous_tb_file, tb_file, "本公司作为出租方", "本公司作为出租方", "本期数", "D2",
                                "column", True, mutable=True, to_column="上年同期数", index_col="承租方名称")
    print("复制本公司作为出租方")
    copy_previous_period_number(previous_tb_file, tb_file, "本公司作为承租方", "本公司作为承租方", "本期数", "D2",
                                "column", True, mutable=True, to_column="上年同期数", index_col="出租方名称")
    print("复制其他关联交易")
    copy_previous_period_number(previous_tb_file, tb_file, "其他关联交易", "其他关联交易", "本期数", "D2",
                                "column", True, mutable=True, to_column="上年同期数", index_col="关联方")


def copy_profit_detail_sheet(previous_tb_file,tb_file):
    '''
    复制上期利润表明细
    :param previous_tb_file: 上期审定表文件路径
    :param tb_file: 本期审定表文件路径
    :return:
    '''
    print("复制税金及附加明细表")
    copy_previous_period_number(previous_tb_file, tb_file,"税金及附加明细表","税金及附加明细表","本期数","D2","column",True,mutable=True,to_column="上年同期数",index_col="项目")
    print("复制销售费用明细表")
    copy_previous_period_number(previous_tb_file, tb_file, "销售费用明细表", "销售费用明细表", "本期数", "D2",
                                "column", True, mutable=True, to_column="上年同期数", index_col="项目")
    # copy_previous_period_number(previous_tb_file, tb_file, "销售费用明细表", "销售费用明细表", "披露项目", "E2",
    #                             "column", True, mutable=True, to_column="披露项目", index_col="项目")
    print("复制管理费用明细表")
    copy_previous_period_number(previous_tb_file, tb_file, "管理费用明细表", "管理费用明细表", "本期数", "D2",
                                "column", True, mutable=True, to_column="上年同期数", index_col="项目")
    print("复制研发费用明细表")
    copy_previous_period_number(previous_tb_file, tb_file, "研发费用明细表", "研发费用明细表", "本期数", "D2",
                                "column", True, mutable=True, to_column="上年同期数", index_col="项目")
    print("复制投资收益明细表")
    copy_previous_period_number(previous_tb_file, tb_file, "投资收益明细表", "投资收益明细表", "本期发生额", "D2",
                                "column", True, mutable=True, to_column="上期发生额", index_col="项目")
    print("复制公允价值变动损益明细表")
    copy_previous_period_number(previous_tb_file, tb_file, "公允价值变动损益明细表", "公允价值变动损益明细表", "本期发生额", "D2",
                                "column", True, mutable=True, to_column="上期发生额", index_col="项目")
    print("复制信用减值损失明细表")
    copy_previous_period_number(previous_tb_file, tb_file, "信用减值损失明细表", "信用减值损失明细表",
                                "本期发生额", "D2",
                                "column", True, mutable=True, to_column="上期发生额", index_col="项目")
    print("复制资产减值损失明细表")
    copy_previous_period_number(previous_tb_file, tb_file, "资产减值损失明细表", "资产减值损失明细表",
                                "本期发生额", "D2",
                                "column", True, mutable=True, to_column="上期发生额", index_col="项目")
    print("复制资产处置收益明细表")
    copy_previous_period_number(previous_tb_file, tb_file, "资产处置收益明细表", "资产处置收益明细表",
                                "本期发生额", "D2",
                                "column", True, mutable=True, to_column="上期发生额", index_col="项目")
    print("复制营业外收入明细表")
    copy_previous_period_number(previous_tb_file, tb_file, "营业外收入明细表", "营业外收入明细表",
                                "本期发生额", "D2",
                                "column", True, mutable=True, to_column="上期发生额", index_col="项目")
    print("复制营业外支出明细表")
    copy_previous_period_number(previous_tb_file, tb_file, "营业外支出明细表", "营业外支出明细表",
                                "本期发生额", "D2",
                                "column", True, mutable=True, to_column="上期发生额", index_col="项目")
    print("复制所得税费用明细表")
    copy_previous_period_number(previous_tb_file, tb_file, "所得税费用明细表", "所得税费用明细表",
                                "本期发生额", "D2",
                                "column",True)



if __name__ == '__main__':
    previous_tb_file = r"D:\audit\audit_report\project\model-义乌城投\义乌市城投供应链管理有限公司_TB - 副本.xlsx"
    tb_file = r"D:\audit\audit_report\project\model-义乌城投\model - 副本.xlsx"
    # copy_TB_cash_owners(previous_tb_file,tb_file)
    # copy_balance_sheet(previous_tb_file,tb_file)
    # copy_profit_sheet(previous_tb_file,tb_file)
    previous_detail_file = r"D:\audit\audit_report\project\model-义乌城投\义乌市城投供应链管理有限公司_detail - 副本.xlsx"
    tb_detail_file = r"D:\audit\audit_report\project\model-义乌城投\detail - 副本.xlsx"
    copy_profit_detail_sheet(previous_detail_file,tb_detail_file)

